SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[GetQuarterly_GrossPropOriginationsByFund]
@FundID VARCHAR(10),
@Date DATETIME 

AS
BEGIN
--DECLARE @fundID VARCHAR(10)='FSIC'
--DECLARE @date DATETIME = '3/29/2013'
DECLARE @randomDate DATETIME
      , @QStart      DATETIME
      , @QEnd        DATETIME
 DECLARE @exportID   INT
 SET @randomDate = @date
 
 DECLARE @outp decimal(38,10) 

 SET @Qstart = DATEADD(q, DATEDIFF(q, 0, @randomDate), 0)
 SET @QEnd   = DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @randomDate) + 1, 0))
 
 SET @exportID =
 (
          SELECT   TOP 1 ExportId
          FROM     FSFundModel_VALCOMM.Reporting.Export
          WHERE    NAVDate <= @QEnd
          AND
                   FundId = @FundID
          ORDER BY ExportId DESC
 )
 --SELECT i.InstrName , t.Proceeds GrossOriginations, t.TradeAmount
 SET @outp = (SELECT SUM(ABS(t.Proceeds)) GrossOriginations
 FROM   FSFundModel_VALCOMM.Reporting.Trade t

 INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i ON i.InstrumentId = t.InstrumentId 
 AND i.ExportId = t.ExportId

 WHERE  t.TradeDate BETWEEN @QStart AND @QEnd	
 AND
        t.InstrumentId IN
                          (
                          
                          SELECT DISTINCT(InstrumentId)
                          FROM            FSFundModel_VALCOMM.Reporting.Asset a
                          WHERE           a.PropReason = 'Proprietary'
                          AND
                                          a.ExportId =
                                          (
                                                 SELECT MAX(ExportId)
                                                 FROM   FSFundModel_VALCOMM.Reporting.Export
                                                 WHERE  FundId = 'FSIC'
                                          )
                          )
 AND
        t.TradeType = 'Purchase'
 AND
        t.ExportId = @exportID)
 
 --ORDER BY i.instrname asc
 RETURN @outp

 END
 



GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/SEC', 'SCHEMA', N'dbo', 'PROCEDURE', N'GetQuarterly_GrossPropOriginationsByFund', NULL, NULL
GO
